﻿Imports System.Data
Imports System.Data.SqlClient

Public Class SinhVienDAO1
    Dim cnn As New SqlConnection()
    Dim da As New SqlDataAdapter()
    Dim cmd As New SqlCommand()
    Function LoadSV() As DataTable
        cnn.ConnectionString = "Server=OPENSUSE-PC\SQLEXPRESS; Database=QLDangKyHocPhan; Trusted_Connection=True"
        cmd = New SqlCommand("sp_XuatSV", cnn)
        cmd.CommandType = CommandType.StoredProcedure
        cnn.Open()
        cmd.ExecuteNonQuery()
        da = New SqlDataAdapter()
        da.SelectCommand = cmd
        Dim dtb As New DataTable()
        da.Fill(dtb)
        cnn.Close()
        Return dtb
    End Function

    Sub ThemSV(ByVal sv As SinhVienDTO)
        cnn.ConnectionString = "Server=OPENSUSE-PC\SQLEXPRESS; Database=QLDangKyHocPhan; Trusted_Connection=True"
        cmd = New SqlCommand("sp_ThemSinhVien", cnn)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.Add("@MaSinhVien", SqlDbType.VarChar, 10)
        cmd.Parameters.Add("@HoTen", SqlDbType.NVarChar, 50)
        cmd.Parameters.Add("@SoDienThoai", SqlDbType.VarChar, 20)
        cmd.Parameters.Add("@NgaySinh", SqlDbType.DateTime)
        cmd.Parameters.Add("@GioiTinh", SqlDbType.NVarChar, 5)

        cmd.Parameters("@MaSinhVien").Value = sv.MaSinhVien
        cmd.Parameters("@HoTen").Value = sv.HoTen
        cmd.Parameters("@SoDienThoai").Value = sv.SoDienThoai
        cmd.Parameters("@NgaySinh").Value = sv.NgaySinh
        cmd.Parameters("@GioiTinh").Value = sv.GioiTinh

        cnn.Open()
        cmd.ExecuteNonQuery()
        da = New SqlDataAdapter()
        cnn.Close()
    End Sub


    Sub XoaSV(ByVal MaSinhVien As String)
        cnn.ConnectionString = "Server=OPENSUSE-PC\SQLEXPRESS; Database=QLDangKyHocPhan; Trusted_Connection=True"
        cmd = New SqlCommand("sp_XoaSinhVien", cnn)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.Add("@MaSinhVien", SqlDbType.VarChar, 10)
        cmd.Parameters("@MaSinhVien").Value = MaSinhVien
        cnn.Open()
        cmd.ExecuteNonQuery()
        cnn.Close()
    End Sub

    Sub UpdateSV(ByVal dtb As DataTable)
        Dim strSQL = "select * from SINHVIEN"
        cnn.ConnectionString = "Server=BAOPHUOC-PC\SQL; Database=QLHS; Trusted_Connection=True"
        da = New SqlDataAdapter(strSQL, cnn)
        Dim builder As New SqlCommandBuilder(da)
        da.Update(dtb)
    End Sub
End Class
